﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;
using System.Text.RegularExpressions;

namespace Pharmacy
{
    class SQLaccessAdmin
    {
        private OleDbConnection Connect = null;

        /// <summary>
        /// Конструктор принемае путь к БД
        /// </summary>
        /// <param name="path"></param>
        public SQLaccessAdmin(string path)
        {

            Connect = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + path);
        }
        /// <summary>
        /// Возврощает максимальное id таблици
        /// </summary>
        /// <param name="nameTable">Имя таблици</param>
        /// <returns></returns>
        public int MaxId(string nameTable)
        {
            try
            {
                string sql_max_id = "SELECT Max(id) FROM " + nameTable + ";";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql_max_id, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                Connect.Close();

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    return tmp;
                }
            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return -1;
            }
            return 0;
        }
        /// <summary>
        /// Добавления нового пользователя
        /// </summary>
        /// <param name="name">Имя/Логин</param>
        /// <param name="password">пароль</param>
        /// <param name="email">Почтовий яшик</param>
        /// <param name="mobail">Мобильний телефон</param>
        /// <param name="job">Место работы</param>
        /// <returns></returns>
        public bool Registration(string name, string password, string email, string mobail, string job)
        {
            bool result = false;
            if (name == "" || password == "" || email == "" || mobail == "" || job == "")
            {
                return result;
            }
            if (!ExceptionEmail(email))
            {
                return result;
            }
            int i = MaxId("[User]");
            i++;
            try
            {
                string sql = "INSERT INTO [User] ( id, Name, [Password], Mail, Tel, Mesto_roboty ) VALUES ('" + i + "', '" + name + "', '" + password + "', '" + email + "', '" + mobail + "', '" + job + "');";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();
                result = true;
                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                result = false;
            }
            return result;

        }
        /// <summary>
        /// Поставка лекарства
        /// </summary>
        /// <param name="id_L">Номер лекарства</param>
        /// <param name="kol_tob">Колличество привезенных лекарств</param>
        /// <returns></returns>
        public int Supplies(int id_L, int kol_tob)
        {
            return 0;
        }
        /// <summary>
        /// Добавление лекарств
        /// </summary>
        /// <param name="name">имя</param>
        /// <returns></returns>
        public int AddMedicine(string name)
        {
            int i = MaxId("Lekarstvo");
            i++;
            try
            {
                string sql = "INSERT INTO [Lekarstvo] (id, Name) VALUES(" + i + ", '" + name + "')";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();
                Connect.Close();

            }
            catch (Exception e)
            {
                i = -1;
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return i;
        }
        /// <summary>
        /// Добавление симптомов
        /// </summary>
        /// <param name="id">номер</param>
        /// <param name="name">имя</param>
        /// <returns></returns>
        public bool AddSymptoms(int id, string name)
        {
            int i = selectIdSimptom(name);
            int j = MaxId("poisk");
            j++;
            try
            {
                string sql = "INSERT INTO [poisk] (id, id_likarstvo, id_sastaw) VALUES(" + j + ", " + id + ", " + i + ")";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return false;
        }
        /// <summary>
        /// Поставка товара
        /// </summary>
        /// <param name="id">номер</param>
        /// <param name="kol">колличество</param>
        /// <param name="date">дата</param>
        /// <param name="praice">цена</param>
        /// <returns></returns>
        public bool AddNamber(int id, int kol, int praice, string date)
        {
            int i = MaxId("Nomer");
            i++;
            try
            {
                string sql = "INSERT INTO [Nomer] (id, Kilichestvo, prais, Data) VALUES(" + i + ", '" + kol + "', '" + praice + "', '" + date + "')";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return false;
        }
        /// <summary>
        /// Добавление активного вешества
        /// </summary>
        /// <param name="id">номер</param>
        /// <param name="name">имя</param>
        /// <returns></returns>
        public bool AddActiveMaterial(int id, string name)
        {
            int i = selectIdActiveMaterial(name);
            int j = MaxId("ARt_point");
            j++;
            try
            {
                string sql = "INSERT INTO [ARt_point] (id, id_l, id_a) VALUES(" + j + ", " + id + ", " + i + ")";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return false;
        }
        /// <summary>
        /// Проверка поля ввода почтового ящика
        /// </summary>
        /// <param name="s">email</param>
        /// <returns></returns>
        private bool ExceptionEmail(string s)
        {

            // Присвоение значения из текстового поля
            string Email = s;
            Match match = null;
            // Построение регулярного выражения
            string pattern = @"[.\-_a-z0-9]+@([a-z0-9]+\.)+[a-z]{2,6}";
            Regex regex = new Regex(pattern);
            match = regex.Match(Email);
            //button4.Enabled = false;
            if (match.Value == "")
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        public bool AddForm(int id, string p)
        {
            int i = selectIdForm(p);
            int j = MaxId("Forma_lekarstva");
            j++;
            try
            {
                string sql = "INSERT INTO [Forma_lekarstva] (id, id_likarstvo, id_form) VALUES(" + j + ", " + id + ", " + i + ")";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                Command.ExecuteNonQuery();

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return false;
        }

        /// <summary>
        /// Метод находит id форму вешества.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public int selectIdForm(string p)
        {
            int result = 0;

            try
            {
                string sql = "SELECT Form.id FROM [Form] where id='" + p + "';";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                foreach (DataRow d in t.Rows)
                {
                    Int32.TryParse(d[0].ToString(), out result);
                }

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return result;
        }
        /// <summary>
        /// Метод находит id симптома.
        /// </summary>
        /// <param name="simptom"></param>
        /// <returns></returns>
        public int selectIdSimptom(string simptom)
        {
            int result = 0;

            try
            {
                string sql = "SELECT Semptom.id FROM [Semptom] where name='" + simptom + "';";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                foreach (DataRow d in t.Rows)
                {
                    Int32.TryParse(d[0].ToString(), out result);
                }

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }

            return result;
        }

        /// <summary>
        /// Метод находит id активного вешества.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public int selectIdActiveMaterial(string name)
        {
            int result = 0;

            try
            {
                string sql = "SELECT Art_des.id FROM [Art_des] where id='" + name + "';";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                foreach (DataRow d in t.Rows)
                {
                    Int32.TryParse(d[0].ToString(), out result);
                }

                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return result;
        }
    }
}
